package gd.qaq.blue.bluespick.util;

import com.alibaba.fastjson.JSON;
import gd.qaq.blue.bluespick.BluesPick;
import gd.qaq.blue.bluespick.dto.AreaData;
import gd.qaq.blue.bluespick.dto.PickBlockData;
import gd.qaq.blue.bluespick.util.area.Area;
import org.bukkit.Bukkit;
import org.bukkit.Location;
import org.bukkit.block.Block;
import org.bukkit.inventory.ItemStack;
import org.json.simple.JSONObject;

import java.sql.*;
import java.util.*;

@SuppressWarnings("all")
public class JDBCUtils extends SqlHelper {
    public JDBCUtils(final String dbFileName) {
        super(dbFileName);
        this.createDB();
    }

    public boolean createDB() {
        try {
            final DatabaseMetaData dbMetaData = this.conn.getMetaData();
            final String[] types = {"TABLE"};
            final ResultSet tabs = dbMetaData.getTables(null, null, "tb_picks", types);
            final ResultSet tabs1 = dbMetaData.getTables(null, null, "tb_areas", types);
            final ResultSet tabs2 = dbMetaData.getTables(null, null, "tb_drops", types);
            final ResultSet tabs4 = dbMetaData.getTables(null, null, "tb_needs", types);
            if (tabs.next() && tabs1.next() && tabs2.next()&&tabs4.next()) {
                return true;
            }
            //这是采集物数据表
            final String sql = "CREATE TABLE `tb_picks` ( " +
                    "  `id` integer PRIMARY KEY autoincrement,  " +
                    "  `areaName` varchar NOT NULL,\n" +
                    "  `pickName` varchar NOT NULL,\n" +
                    "  `pickItemId` varchar NOT NULL,\n" +
                    "  `pickTime` varchar NOT NULL,\n" +
                    "  `world` varchar NOT NULL,\n" +
                    "  `x` double NOT NULL,\n" +
                    "  `y` double NOT NULL,\n" +
                    "  `z` double NOT NULL,\n" +
                    "  `replaceTime` varchar NOT NULL)";
            //这是所需物品数据表
            final String sql4 = "CREATE TABLE `tb_needs` ( " +
                    "  `id` integer PRIMARY KEY autoincrement,\n  " +
                    "  `itemSerializeData` varchar NOT NULL,\n "+
                    "  `position` integer NOT NULL,\n "+
                    "  `areaName` varchar NOT NULL,\n "+
                    "  `pickItemId` varchar NOT NULL\n)";
            //这是区域数据表
            final String sql2 = "CREATE TABLE `tb_areas` ( " +
                    "  `id` integer PRIMARY KEY autoincrement,  " +
                    "  `areaName` varchar NOT NULL," +
                    "firstWorldName varchar NOT NULL,firstLocationX varchar NOT NULL,firstLocationY varchar NOT NULL,firstLocationZ varchar NOT NULL," +
                    "secondWorldName varchar NOT NULL,secondLocationX varchar NOT NULL,secondLocationY varchar NOT NULL,secondLocationZ varchar NOT NULL)";
            //这是掉落物数据表
            final String sql3 = "CREATE TABLE `tb_drops` ( " +
                    "  `id` integer PRIMARY KEY autoincrement,  " +
                    "  `itemSerializeData` varchar NOT NULL,\n "+
                    "  `position` integer NOT NULL,\n "+
                    "  `areaName` varchar NOT NULL,\n "+
                    "  `pickItemId` varchar NOT NULL\n)";
            this.query(sql).execute();
            this.query(sql2).execute();
            this.query(sql3).execute();
            this.query(sql4).execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 添加采集物
     * @param data
     * @return
     */
    public boolean addPick(PickBlockData data) {
        if (data == null) {
            return false;
        }
        String areaName = data.getAreaData().getAreaName();
        String pickItemId = data.getPickItemId();
        try {
            String sql1 = "SELECT * FROM tb_picks WHERE areaName=? AND pickItemId=?";
            final PreparedStatement pre1 = this.query(sql1);
            pre1.setString(1, areaName);
            pre1.setString(2, pickItemId);
            ResultSet rs = pre1.executeQuery();
            if (rs.next()) {
                //如果已经存在,就删除这个区域
                sql1 = "DELETE FROM tb_picks WHERE areaName=? AND pickItemId=?";
                final PreparedStatement pre12 = this.query(sql1);
                pre12.setString(1, areaName);
                pre12.setString(2, pickItemId);
                pre12.executeUpdate();
            }
            final String sql = "INSERT INTO tb_picks " +
                    "(areaName,pickItemId,pickTime,replaceTime,pickName,x,y,z,world) " +
                    " values (?,?,?,?,?,?,?,?,?);";
            final PreparedStatement pre = this.query(sql);
            pre.setString(1, areaName);
            pre.setString(2, pickItemId);
            pre.setString(3, String.valueOf(data.getPickTime()));
            pre.setString(4, String.valueOf(data.getReplaceTime()));
            pre.setString(5, data.getPickName());
            pre.setDouble(6, data.getX());
            pre.setDouble(7, data.getY());
            pre.setDouble(8, data.getZ());
            pre.setString(9, data.getWorld());
            if (pre.executeUpdate() == 0) {
                return false;
            }
            pre.close();
            pre1.close();
            rs.close();
        } catch (SQLException e) {
            return false;
        }
        return true;
    }

    public PickBlockData getPickInfo(Block block) {
        Location location = block.getLocation();
        AreaData currentArea = AreaUtil.getCurrentArea(location);
        int typeId = block.getTypeId();
        byte data = block.getData();
        if (currentArea == null) {
            return null;
        }
        return getPick(block,currentArea, typeId + ":" + data);
    }

    /**
     * 获取所有采集物
     * @param areaData
     * @param id
     * @return
     */
    public List<PickBlockData> getAllPick() {
        List<PickBlockData> picks = new ArrayList<>();
        final String sql = "SELECT * FROM tb_picks";
        BluesPick.plugin.getLogger().info("进入查询了");
        try {
            ResultSet rs = null;
            final PreparedStatement pre = this.query(sql);
            rs = pre.executeQuery();

            while (rs.next()) {
                picks.add( new PickBlockData(
                        rs.getString("world"),
                        rs.getDouble("x"),
                        rs.getDouble("y"),
                        rs.getDouble("z"),
                        null,
                        rs.getString("pickName"),
                        rs.getString("pickItemId"),
                        Double.parseDouble(rs.getString("pickTime")),
                        Double.parseDouble(rs.getString("replaceTime")),
                        null,null));
            }
            rs.close();
            pre.close();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            return picks;
        }
    }
    /**
     * 获取采集物
     * @param areaData
     * @param id
     * @return
     */
    public PickBlockData getPick(Block block,AreaData areaData, String id) {
        final String sql = "SELECT * FROM tb_picks WHERE areaName=? AND pickItemId=?";
        BluesPick.plugin.getLogger().info("进入查询了");
        ResultSet rs = null;
        PreparedStatement pre = null;
        try {
            pre = this.query(sql);
            pre.setString(1, areaData.getAreaName());
            pre.setString(2, id);
            rs = pre.executeQuery();
            PickBlockData data = null;
            while (rs.next()) {
                data = new PickBlockData(
                        block.getWorld().getName(),
                        block.getLocation().getX(),
                        block.getLocation().getY(),
                        block.getLocation().getZ(),
                        areaData,
                        rs.getString("pickName"),
                        rs.getString("pickItemId"),
                        Double.parseDouble(rs.getString("pickTime")),
                        Double.parseDouble(rs.getString("replaceTime")),
                        Arrays.asList(getItems(false,areaData.getAreaName(), id)),Arrays.asList(getItems(true,areaData.getAreaName(), id)));
            }
            return data;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }finally {
            try {
                rs.close();
                pre.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }


    public boolean removePick(final PickBlockData data) {
        if (data == null) {
            return false;
        }
        PreparedStatement pre = null;
        try { //如果已经存在,就删除这个区域
            final String sql = "DELETE FROM tb_picks WHERE areaName=? AND pickItemId=?";
            pre = this.query(sql);
            pre.setString(1, data.getAreaData().getAreaName());
            pre.setString(2, data.getPickItemId());
            if (pre.executeUpdate() == 0) {
                return false;
            }
        } catch (SQLException e) {
            return false;
        }finally {
            try {
                pre.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return true;
    }

    public boolean addArea(AreaData areaData) {
        if (areaData == null) {
            return false;
        }
        Area area = areaData.getArea();
        String areaName = areaData.getAreaName();
        try {
            String sql1 = "SELECT * FROM tb_areas WHERE areaName=?";
            final PreparedStatement pre1 = this.query(sql1);
            pre1.setString(1, areaName);
            ResultSet rs = pre1.executeQuery();
            if (rs.next()) {
                //如果已经存在,就删除这个区域
                sql1 = "DELETE FROM tb_areas WHERE areaName=?";
                final PreparedStatement pre12 = this.query(sql1);
                pre12.setString(1, areaName);
                pre12.executeUpdate();
            }
            Location maxLocation = area.getMaxLocation();
            Location minLocation = area.getMinLocation();
            final String sql = "INSERT INTO tb_areas " +
                    "(areaName,firstWorldName,firstLocationX,firstLocationY,firstLocationZ,secondWorldName,secondLocationX,secondLocationY,secondLocationZ) " +
                    " values (?,?,?,?,?,?,?,?,?);";
            final PreparedStatement pre = this.query(sql);
            pre.setString(1, areaName);
            pre.setString(2, maxLocation.getWorld().getName());
            pre.setString(3, String.valueOf(maxLocation.getX()));
            pre.setString(4, String.valueOf(maxLocation.getY()));
            pre.setString(5, String.valueOf(maxLocation.getZ()));
            pre.setString(6, minLocation.getWorld().getName());
            pre.setString(7, String.valueOf(minLocation.getX()));
            pre.setString(8, String.valueOf(minLocation.getY()));
            pre.setString(9, String.valueOf(minLocation.getZ()));
            if (pre.executeUpdate() == 0) {
                return false;
            }
            rs.close();
            pre.close();
            pre1.close();
        } catch (SQLException e) {
            return false;
        }
        return true;
    }

    public List<AreaData> getAreas() {
        final String sql = "SELECT * FROM tb_areas;";
        try {
            ResultSet rs = null;
            final PreparedStatement pre = this.query(sql);
            rs = pre.executeQuery();
            List<AreaData> areaDataList = new ArrayList<>();
            while (rs.next()) {
                AreaData areaData = new AreaData();
                areaData.setArea(new Area(
                        new Location(Bukkit.getWorld(rs.getString("firstWorldName")),
                                Double.parseDouble(rs.getString("firstLocationX")),
                                Double.parseDouble(rs.getString("firstLocationY")),
                                Double.parseDouble(rs.getString("firstLocationZ"))),
                        new Location(Bukkit.getWorld(rs.getString("secondWorldName")),
                                Double.parseDouble(rs.getString("secondLocationX")),
                                Double.parseDouble(rs.getString("secondLocationY")),
                                Double.parseDouble(rs.getString("secondLocationZ")))));
                areaData.setAreaName(rs.getString("areaName"));
                areaDataList.add(areaData);
            }
            rs.close();
            pre.close();
            return areaDataList;
        } catch (SQLException e) {
            return null;
        }
    }


    public boolean removeArea(final AreaData areaData) {
        if (areaData == null) {
            return false;
        }
        final String sql = "DELETE FROM tb_areas WHERE areaName = ?";
        try {
            final PreparedStatement pre = this.query(sql);
            pre.setString(1, areaData.getAreaName());
            if (pre.executeUpdate() == 0) {
                return false;
            }
            pre.close();
        } catch (SQLException e) {
            return false;
        }
        return true;
    }


    public gd.qaq.blue.bluespick.entity.ItemStack[] getItems(boolean isNeed, String areaName, String id) {
        List<gd.qaq.blue.bluespick.entity.ItemStack> list = new ArrayList<>();
//        try {
//            String sql1 = "SELECT * FROM "+(isNeed?"tb_needs":"tb_drops")+" WHERE areaName=? and pickItemId=?";
//            final PreparedStatement pre1 = this.query(sql1);
//            pre1.setString(1,areaName);
//            pre1.setString(2, id);
//            ResultSet rs = pre1.executeQuery();
//            BluesPick.plugin.getLogger().info("有数据");
//            while (rs.next()) {
//                gd.qaq.blue.bluespick.entity.ItemStack itemStack = new gd.qaq.blue.bluespick.entity.ItemStack();
//                itemStack.setItem(ItemStack.deserialize(JSON.parseObject(rs.getString("itemSerializeData"), HashMap.class)));
//                itemStack.setPosition(rs.getInt("position"));
//                list.add(itemStack);
//            }
//            rs.close();
//            pre1.close();
//        }catch (Exception e){
//            e.printStackTrace();
//        }finally{
        // TODO: 2021/7/19 改为yaml存储形式  


            return list.toArray(new gd.qaq.blue.bluespick.entity.ItemStack[]{});
//        }
    }
    /**
     * "CREATE TABLE `tb_needs` ( " +
     *                     "  `id` integer PRIMARY KEY autoincrement,\n  " +
     *                     "  `itemSerializeData` varchar NOT NULL,\n "+
     *                     "  `areaName` varchar NOT NULL,\n "+
     *                     "  `pickItemId` varchar NOT NULL\n)";
     * @param items
     * @return
     */
    public boolean setItems(boolean isNeed, PickBlockData data, gd.qaq.blue.bluespick.entity.ItemStack[] items) {
//        try {
//            String sql1 = "SELECT * FROM "+(isNeed?"tb_needs":"tb_drops")+" WHERE areaName=? and pickItemId=?";
//            final PreparedStatement pre1 = this.query(sql1);
//            pre1.setString(1, data.getAreaData().getAreaName());
//            pre1.setString(2, data.getPickItemId());
//            ResultSet rs = pre1.executeQuery();
//            if (rs.next()) {
//                BluesPick.plugin.getLogger().info("有数据，删除");
//                //如果已经存在,就清空所有物品
//                sql1 = "DELETE FROM "+(isNeed?"tb_needs":"tb_drops")+" WHERE areaName=? and pickItemId=?";
//                final PreparedStatement pre12 = this.query(sql1);
//                pre12.setString(1, data.getAreaData().getAreaName());
//                pre12.setString(2, data.getPickItemId());
//                pre12.executeUpdate();
//                pre12.close();
//            }
//            BluesPick.plugin.getLogger().info("添加了物品");
//            //添加物品
//            for (gd.qaq.blue.bluespick.entity.ItemStack item : items) {
//                if(item==null)break;
//                final String sql = "INSERT INTO "+(isNeed?"tb_needs":"tb_drops")+" " +
//                        "(itemSerializeData,position,areaName,pickItemId)" +
//                        " values (?,?,?,?);";
//                final PreparedStatement pre = this.query(sql);
//                pre.setString(1, JSONObject.toJSONString(item.getItem().serialize()));
//                pre.setInt(2, item.getPosition());
//                pre.setString(3, data.getAreaData().getAreaName());
//                pre.setString(4, data.getPickItemId());
//                if (pre.executeUpdate() == 0) {
//                    return false;
//                }
//                pre.close();
//            }
//            rs.close();
//            pre1.close();
//        } catch (SQLException e) {
//            e.printStackTrace();
//            return false;
//        }
        // TODO: 2021/7/19 改为yaml存储形式

        return true;
    }
}
